// import { exists, mkdir } from "@tauri-apps/plugin-fs";
import Database from "@tauri-apps/plugin-sql";

export class SqliteSettings {

    file: string;

    table: string;

    constructor(file: string, tableName?: string) {
        this.file = file;
        this.table = tableName ? tableName : 'g01_settings';
        this._initDatabase(file, this.table).then();
    }

    public async all(): Promise<Array<any>> {
        let db = await this._getDatabase();
        if (db == null) {
            return [];
        }
        return await allG(db, this.table);
    }

    public async getValuesByCategory(category: string): Promise<any> {
        let db = await this._getDatabase();
        if (db == null) {
            return null;
        }
        return await getValuesByCategoryG(db, this.table, category);
    }

    public async getValueByCategory(category: string, name: string, defaultValue?: any): Promise<any> {
        let db = await this._getDatabase();
        if (db == null) {
            return null;
        }
        return await getValueByCategoryG(db, this.table, category, name, defaultValue);
    }

    public async setValueByCategory(category: string, name: string, value: string, valueType?: string): Promise<void> {
        let db = await this._getDatabase();
        if (db == null) {
            return;
        }
        await setValueByCategoryG(db, this.table, category, name, value, valueType)
    }

    public async getValue(name: string, defaultValue?: any): Promise<any> {
        let db = await this._getDatabase();
        if (db == null) {
            return null;
        }
        return await getValueG(db, this.table, name, defaultValue);
    }

    public async setValue(name: string, value: string, valueType?: string): Promise<void> {
        let db = await this._getDatabase();
        if (db == null) {
            return;
        }
        return await setValueG(db, this.table, name, value, valueType);
    }

    public async removeByCategory(category: string): Promise<void> {
        let db = await this._getDatabase();
        if (db == null) {
            return;
        }
        await removeByCategoryG(db, this.table, category);
    }

    public async removeByCategoryAndName(category: string, name: string): Promise<void> {
        let db = await this._getDatabase();
        if (db == null) {
            return;
        }
        await removeByCategoryAndNameG(db, this.table, category, name);
    }
    public async remove(name: string): Promise<void> {
        let db = await this._getDatabase();
        if (db == null) {
            return;
        }
        await removeG(db, this.table, name);
        return;
    }

    async _initDatabase(fileSrc: string, table: string) {
        // 查看是否存在
        try {
            console.log(fileSrc);
            const db = await Database.load(`sqlite:${fileSrc}`);

            let tableName = table ? table : 'g01_settings';
            //建表
            db.execute(`
                CREATE TABLE if not exists ${tableName}(
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    category TEXT DEFAULT '',
                    name TEXT DEFAULT '',
                    value TEXT DEFAULT '',
                    value_type TEXT DEFAULT '',
                    description TEXT DEFAULT '',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );
                CREATE TRIGGER if not exists ${tableName}_updated AFTER UPDATE ON ${tableName} 
                BEGIN
                UPDATE ${tableName} SET updated_at = CURRENT_TIMESTAMP WHERE id = new.id;
                END;
            `);

            return db;
        } catch (err) {
            console.log(err);
        }
    }

    async _getDatabase(): Promise<Database | null> {
        try {
            const db = await Database.load(`sqlite:${this.file}`);
            return db;
        } catch (err) {
            console.log(err);
        }
        return null;
    }
}


export const allG = async (db: Database, table: string): Promise<Array<any>> => {
    let items: Array<any> = await db.select(`select * from ${table} order by id`);
    console.log(items);
    if (items) {
        return items;
    }
    return []
}

export const getValuesByCategoryG = async (db: Database, table: string, category: string): Promise<any> => {
    let items: Array<any> = await db.select(`select * from ${table} where category = $1`, [category]);
    console.log(items);
    if (items) {
        return items;
    }
    return [];
}

export const getValueByCategoryG = async (db: Database, table: string, category: string, name: string, defaultValue?: any): Promise<any> => {
    let items: Array<any> = await db.select(`select * from ${table} where category = $1 and name = $2`, [category, name]);
    console.log(items);
    if (items && items.length > 0) {
        return items[0].value;
    }
    return defaultValue;
}

export const setValueByCategoryG = async (db: Database, table: string, category: string, name: string, value: string, valueType?: string): Promise<void> => {
    let vt = valueType ? valueType : 'String';

    let count: Array<any> = await db.select(`select count(1) cnt from ${table} where category = $1 and name = $2`, [category, name]);

    console.log(count);
    if (count && count.length > 0 && count[0].cnt > 0) {
        //存在
        await db.execute(`update ${table} set value = $1 where category = $2 and name = $3 `, [value, category, name]);
    } else {
        //插入
        await db.execute(`insert into  ${table}(category,name,value,value_type) values($1,$2,$3,$4) `, [category, name, value, vt]);
    }
}

export const getValueG = async (db: Database, table: string, name: string, defaultValue?: any): Promise<any> => {
    let items: Array<any> = await db.select(`select * from ${table} where name = $1`, [name]);
    console.log(items);
    if (items && items.length > 0) {
        return items[0].value;
    }
    return defaultValue;
}

export const setValueG = async (db: Database, table: string, name: string, value: string, valueType?: string): Promise<void> => {
    let vt = valueType ? valueType : 'String';

    let count: Array<any> = await db.select(`select count(1) cnt from ${table} where name = $1`, [name]);

    console.log(count);
    if (count && count.length > 0 && count[0].cnt > 0) {
        //存在
        await db.execute(`update ${table} set value = $1 where name = $2 `, [value, name]);
    } else {
        //插入
        await db.execute(`insert into  ${table}(name,value,value_type) values($1,$2,$3) `, [name, value, vt]);
    }
}

export const removeByCategoryG = async (db: Database, table: string, category: string): Promise<void> => {
    await db.execute(`delete from ${table} where category = $1 `, [category]);
}

export const removeByCategoryAndNameG = async (db: Database, table: string, category: string, name: string): Promise<void> => {
    await db.execute(`delete from ${table} where category = $1 and name = $2`, [category, name]);
}
export const removeG = async (db: Database, table: string, name: string): Promise<void> => {
    await db.execute(`delete from ${table} where name = $1 `, [name]);
}

export default {};